﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using IDAL;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace DAL
{
    public class FlowersDal:IFlowersDal
    {
        public static string Conns = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        /// <summary>
        /// 花卉展示
        /// </summary>
        /// <returns></returns>
        public List<FlowersModel> FlowersShow()
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = "select FlowersID,FlowersName,FlowersStock,PurchasePrice,Sales_unit_price,FlowersImg, FlowerState,FlowersAddTime from FlowersTable";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<FlowersModel> list = new List<FlowersModel>();
                while (read.Read())
                {
                    FlowersModel mo = new FlowersModel();
                    mo.FlowersID = Convert.ToInt32(read["FlowersID"]);
                    mo.FlowersName = read["FlowersName"].ToString();
                    mo.FlowersStock = Convert.ToInt32(read["FlowersStock"]);
                    mo.PurchasePrice = Convert.ToInt32(read["PurchasePrice"]);
                    mo.Sales_unit_price = Convert.ToInt32(read["Sales_unit_price"]);
                    mo.FlowerState = Convert.ToInt32(read["FlowerState"]);
                    mo.FlowersImg = read["FlowersImg"].ToString();
                    mo.FlowersAddTime =Convert.ToDateTime(read["FlowersAddTime"]);
                    list.Add(mo);

                }
                conn.Close();
                return list;
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="FlowersName"></param>
        /// <returns></returns>
        public List<FlowersModel> Flowerschaxun(string FlowersName)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = "select FlowersID,FlowersName,FlowersStock,PurchasePrice,Sales_unit_price,FlowersImg, FlowerState,FlowersAddTime from FlowersTable where FlowersName like '%" + FlowersName + "%'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<FlowersModel> list = new List<FlowersModel>();
                while (read.Read())
                {
                    FlowersModel mo = new FlowersModel();
                    mo.FlowersID = Convert.ToInt32(read["FlowersID"]);
                    mo.FlowersName = read["FlowersName"].ToString();
                    mo.FlowersStock = Convert.ToInt32(read["FlowersStock"]);
                    mo.PurchasePrice = Convert.ToInt32(read["PurchasePrice"]);
                    mo.Sales_unit_price = Convert.ToInt32(read["Sales_unit_price"]);
                    mo.FlowerState = Convert.ToInt32(read["FlowerState"]);
                    mo.FlowersImg = read["FlowersImg"].ToString();
                    mo.FlowersAddTime = Convert.ToDateTime(read["FlowersAddTime"]);
                    list.Add(mo);

                }
                conn.Close();
                return list;
            }
        }

        /// <summary>
        /// 反填
        /// </summary>
        /// <returns></returns>
        public FlowersModel Flowersfantian(int FlowersID)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = "select FlowersID,FlowersName,FlowersStock,PurchasePrice,Sales_unit_price, FlowerState from FlowersTable where FlowersID=" + FlowersID;
                SqlCommand cmd = new SqlCommand(sql, conn);
                FlowersModel mo = new FlowersModel();
                SqlDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    mo.FlowersID = Convert.ToInt32(read["FlowersID"]);
                    mo.FlowersName = read["FlowersName"].ToString();
                    mo.FlowersStock = Convert.ToInt32(read["FlowersStock"]);
                    mo.PurchasePrice = Convert.ToInt32(read["PurchasePrice"]);
                    mo.Sales_unit_price = Convert.ToInt32(read["Sales_unit_price"]);
                }
                conn.Close();
                return mo;
            }
        }
        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int Flowersbianji(FlowersModel m)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = "update FlowersTable set FlowersName='" + m.FlowersName + "',FlowersStock='" + m.FlowersStock + "',PurchasePrice='" + m.PurchasePrice + "',Sales_unit_price='" + m.Sales_unit_price + "'  where FlowersID='" + m.FlowersID + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }

        /// <summary>
      /// 修改状态
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
        public int FlowersUpt(int id)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = string.Format("update FlowersTable set FlowerState=1 where FlowersID='{0}'", id);
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }



      /// <summary>
      /// 停用状态
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
        public int FlowersDel(int id)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = string.Format("update FlowersTable set FlowerState=0 where FlowersID='{0}'", id);
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }


        
    }
}
